﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using RestService.Models;

namespace RestService
{
    public class DataConnection
    {
        private String _provide = "System.Data.SqlClient";

        private const String Connection =
            ("Data Source=clea.mic.psnc.pl;Initial Catalog=proinz.studentlive.pl;User ID=proinz;Password=JtDlzB1j41");

        private readonly DbProviderFactory _factory = DbProviderFactories.GetFactory("System.Data.SqlClient");

        // zwraca game_id
        public string createGame(string gameName, string max_players, string max_points)
        {
            string game_id = null;

            //DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText =
                        com.CommandText =
                        "insert into Game (name,max_players,max_points,flag_a,flag_b,points_a,points_b) values (@name,@max_players,@max_points,@flag_a,@flag_b,@points_a,@points_b)";

                    DbParameter p;

                    p = com.CreateParameter();
                    p.DbType = DbType.String;
                    p.Value = gameName;
                    p.ParameterName = "@name";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = int.Parse(max_players);
                    p.ParameterName = "@max_players";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = int.Parse(max_points);
                    p.ParameterName = "@max_points";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Boolean;
                    p.Value = true;
                    p.ParameterName = "@flag_a";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Boolean;
                    p.Value = true;
                    p.ParameterName = "@flag_b";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = 0;
                    p.ParameterName = "@points_a";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = 0;
                    p.ParameterName = "@points_b";
                    com.Parameters.Add(p);

                    con.Open();
                    com.ExecuteNonQuery();

                }
                game_id = getGameId(gameName);
            }
            return game_id;
        }

        // zwraca id game_id
        public string getGameId(string gameName)
        {
            string res = null;
            //DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select id_game from game where name='" + gameName + "' ORDER BY id_game DESC";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    reader.Read();
                    res += reader[0];
                }
            }
            return res;
        }


        public string createArea(string gameId, int ax, int ay, int bx, int by, int cx, int cy, int dx, int dy)
        {
            //    DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText =
                        com.CommandText =
                        "insert into Area (id_game,latitude_area_a, longitude_area_a,latitude_area_b,longitude_area_b,latitude_area_c,longitude_area_c,latitude_area_d,longitude_area_d) values (@id_game,@latitude_area_a,@longitude_area_a,@latitude_area_b,@longitude_area_b,@latitude_area_c,@longitude_area_c,@latitude_area_d,@longitude_area_d)";

                    DbParameter p;

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = gameId;
                    p.ParameterName = "@id_game";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = ax;
                    p.ParameterName = "@latitude_area_a";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = ay;
                    p.ParameterName = "@longitude_area_a";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = bx;
                    p.ParameterName = "@latitude_area_b";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = by;
                    p.ParameterName = "@longitude_area_b";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = cx;
                    p.ParameterName = "@latitude_area_c";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = cy;
                    p.ParameterName = "@longitude_area_c";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = dx;
                    p.ParameterName = "@latitude_area_d";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = dy;
                    p.ParameterName = "@longitude_area_d";
                    com.Parameters.Add(p);

                    con.Open();
                    com.ExecuteNonQuery();
                }
            }
            return null;
        }

        // zwraca id gracza
        public string createPlayer(string gameId, string name, bool team, string latitude, string longitude)
        {
            //  DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText =
                        com.CommandText =
                        "insert into Players (id_game,name,team,flag,latitude,longitude) values (@id_game,@name,@team,@flag,@latitude,@longitude)";

                    DbParameter p;
                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = int.Parse(gameId);
                    p.ParameterName = "@id_game";
                    com.Parameters.Add(p);


                    p = com.CreateParameter();
                    p.DbType = DbType.String;
                    p.Value = name;
                    p.ParameterName = "@name";
                    com.Parameters.Add(p);


                    p = com.CreateParameter();
                    p.DbType = DbType.Boolean;
                    p.Value = team;
                    p.ParameterName = "@team";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Boolean;
                    p.Value = false;
                    p.ParameterName = "@flag";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = int.Parse(latitude);
                    p.ParameterName = "@latitude";
                    com.Parameters.Add(p);

                    p = com.CreateParameter();
                    p.DbType = DbType.Int32;
                    p.Value = int.Parse(longitude);
                    p.ParameterName = "@longitude";
                    com.Parameters.Add(p);

                    con.Open();
                    com.ExecuteNonQuery();
                }


            }
            return null;
        }

        // zwraca id gry
        public string getPlayerId(string gameName)
        {
            string res = null;
            // DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select id_player from players where name='" + gameName +
                                      "' ORDER BY id_player DESC";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    reader.Read();
                    res += reader[0];
                }
            }
            return res;
        }

        // zwraca id gry
        public string getPlayerTeam(string playerID)
        {
            string res = null;
            // DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select team from players where id_player='" + playerID + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    reader.Read();
                    res += reader[0];
                }
            }
            return res;
        }

        public string howManyPointsToWin(string gameId)
        {
            string res = null;
            // DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select max_points from game where id_game='" + gameId + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    reader.Read();
                    res += reader[0];
                }
            }
            return res;
        }

        public string clearDataBasa(string key)
        {
            if (!key.Equals("admin"))
                return null;

            //DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "DELETE FROM Area";

                    con.Open();
                    com.ExecuteNonQuery();

                    com.CommandText = "DELETE FROM Players";

                    com.ExecuteNonQuery();

                    com.CommandText = "DELETE FROM Game";

                    com.ExecuteNonQuery();
                }
            }
            return null;
        }

        public string showAll(string table)
        {
            string res = null;
            //  DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select * from " + table;

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        res += " <";
                        for (int i = 0; i < reader.FieldCount; i++)
                            res += reader[i] + " ";
                        res += "> ";
                    }
                }
            }
            return res;
        }

        public List<GamesToSend> showAllGames()
        {
            List<GamesToSend> ps = new List<GamesToSend>();
            //  DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "SELECT id_game, name FROM Game";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        GamesToSend p = new GamesToSend();
                        p.id_game = reader.GetInt32(0).ToString();
                        p.name = reader.GetString(1);
                        ps.Add(p);
                    }
                }
            }
            return ps;
        }

        public List<PlayerStatus> getPlayers(string playerID, string gameID, string points)
        {
            List<PlayerStatus> ps = new List<PlayerStatus>();

            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select * from players where id_game='" + gameID + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader.GetInt32(0).Equals(int.Parse(playerID)))
                        {
                            PlayerStatus p = new PlayerStatus();
                            p.team = reader.GetBoolean(3);
                            p.flag = reader.GetBoolean(4);
                            p.latitude = reader.GetInt32(0);
                            p.longitude = int.Parse(points);
                            ps.Add(p);
                        }
                        else
                        {
                            PlayerStatus p = new PlayerStatus();
                            p.team = reader.GetBoolean(3);
                            p.flag = reader.GetBoolean(4);
                            p.latitude = reader.GetInt32(5);
                            p.longitude = reader.GetInt32(6);
                            ps.Add(p);
                        }
                    }
                }
            }
            return ps;
        }

        public List<FlagControll> getPlayersForFlagControl(string gameID, string playerID, string team)
        {
            List<FlagControll> ps = new List<FlagControll>();
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select * from players where id_game='" + gameID + "' and team='" + team + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader.GetInt32(0).Equals(int.Parse(playerID)))
                        {
                            // nasz pozycja
                        }
                        else
                        {
                            FlagControll p = new FlagControll();
                            p.latitude = reader.GetInt32(5);
                            p.longitude = reader.GetInt32(6);
                            ps.Add(p);
                        }
                    }
                }
            }
            return ps;
        }

        public string UpdataPosition(string id_player, string latitude, string longitude)
        {
            string res = null;
            // DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = " UPDATE Players SET latitude='" + latitude + "', longitude='" + longitude +
                                      "' WHERE id_player='" + id_player + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                }
            }
            return null;
        }

        public void ChangeFlagOwner(string id_player)
        {

        }

        public void AddPoint(string id_player)
        {

        }

        public Area getGameArea(string id_game)
        {
            Area area = new Area();
            //  DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select * from Area where id_game='" + id_game + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    reader.Read();
                    area.id_game = reader[1].ToString();
                    area.latitude_area_a = reader[2].ToString();
                    area.longitude_area_a = reader[3].ToString();
                    area.latitude_area_b = reader[4].ToString();
                    area.longitude_area_b = reader[5].ToString();
                    area.latitude_area_c = reader[6].ToString();
                    area.longitude_area_c = reader[7].ToString();
                    area.latitude_area_d = reader[8].ToString();
                    area.longitude_area_d = reader[9].ToString();

                }
            }
            return area;
        }

        public string getPoints(string id_game, string id_player)
        {
            bool team = bool.Parse(getPlayerTeam(id_player));
            string tab_points = null;
            if (team)
                tab_points = "points_a";
            else
                tab_points = "points_b";
            string res = "";
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = "Select " + tab_points + " from game where id_game='" + id_game + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                    reader.Read();
                    res += reader[0];
                }
            }
            return res;
        }

        public void TakeFlag(string id_player)
        {
            // DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = " UPDATE Players SET flag='false' WHERE id_player='" + id_player + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                }
            }
        }

        public void SetFlag(string id_player)
        {
            // DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = " UPDATE Players SET flag='true' WHERE id_player='" + id_player + "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                }
            }
        }

        public void SetPoint(string gameID, string team, string points)
        {
            int point = int.Parse(points);
            point++;
            points = point.ToString();

            String whichTeam = null;
            if (team.Equals("True"))
            {
                whichTeam = "points_a";
            }
            else
            {
                whichTeam = "points_b";
            }
            // DbProviderFactory factory = DbProviderFactories.GetFactory(provide);
            using (DbConnection con = _factory.CreateConnection())
            {
                con.ConnectionString = Connection;
                using (DbCommand com = _factory.CreateCommand())
                {
                    com.Connection = con;
                    com.CommandText = " UPDATE Game SET " + whichTeam + "='" + points + "' WHERE id_game='" + gameID +
                                      "'";

                    con.Open();
                    DbDataReader reader = com.ExecuteReader();
                }
            }
        }

        public bool HasPlayerFlag(string playerId)
        {
            var result = false;
            using (var connection = _factory.CreateConnection())
            {
                if (connection != null)
                {
                    connection.ConnectionString = Connection;

                    using (var command = _factory.CreateCommand())
                    {
                        if (command != null)
                        {
                            command.Connection = connection;
                            command.CommandText = "Select flag from players where id_player='" + playerId + "'";

                            connection.Open();
                            var reader = command.ExecuteReader();
                            reader.Read();
                            result = reader.GetBoolean(0);
                        }
                    }
                }
            }
            return result;
        }
    }
}
